package com.navi.loaddata.loadService.impl.spcData;

import com.navi.loaddata.core.errorLog.ErrorCode;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.pojo.SpcChartDataTmp;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MesRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import com.navi.loaddata.core.util.ExecuteSqlCallable;
import com.navi.loaddata.core.util.JdbcUtil2;
import com.navi.loaddata.core.util.LoggerUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.util.StringUtil;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import java.math.BigDecimal;
import java.sql.SQLIntegrityConstraintViolationException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.*;

@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager",
        rollbackFor = {RuntimeException.class, Exception.class})
public class LoadSpcDataToGpService {
    private static final Logger logger = LoggerFactory.getLogger(LoadSpcDataToGpService.class);
   private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//yyyy-mm-dd hh:mm:ss   yyyyMMddHHmmss
    private static SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddHHmmss");//yyyy-mm-dd hh:mm:ss   yyyyMMddHHmmss
    public static final String MES_QUERY_COLUMN =" CHARTID ,CONTROLCHARTTYPE ,CHARTOWNER ,FACTORYNAME ,SUBFACTORYNAME ," +
            "     CONTROLDATASEQ ,DCDATAID ,TIMEKEY ,DCTYPE ,CHARTTYPE_EXT ," +
            "     MATERIALNAME ,ALIASITEMNAME ,PRODUCTSPECNAME ,ECCODE ,RUNMODE ,OWNERCODE ,RTC ,QTAP ,EventUser," +
            "     EVENTTIME ,PROCESSOPERATIONNAME ,MACHINENAME ,UNITNAME ,RECIPENAME ," +
            "     EVENTTIME_PRE ,PROCPROCESSOPERATIONNAME ,PROCMACHINENAME ,PROCUNITNAME ,PROCRECIPENAME ," +
            "     USPEC ,TARGET ,LSPEC ,UCL1 ,CL1 ,LCL1 ,UCL2 ,CL2 ,LCL2 ,UCL3 ,CL3 ,LCL3 ," +
            "     D_XBAR ,D_SDEV ,D_RNG ,D_UNF1 ,D_UNF2 ,D_MAX ,D_MIN ,D_X ,D_RM ,D_NP ,D_P ,D_C ,D_U ,DEL_MARK ," +
            "     OOS ,OOC1 ,OOC2 ,OOC3 ,OOR1 ,OOR2 ,OOR3 ,SITECOUNT ,RAWDATAS";

    public static final String GP_QUERY_COLUMN =" chartid,charttype,factoryname,subfactoryname,controldataseq,dcdataid,db_timestamp_src," +
            "glass_id,eventtime,eventtime_pre,itemname,productspecname,eccode,processoperationname,machinename,unitname,recipename," +
            "procprocessoperationname,procmachinename,procunitname,procrecipename,runmode,ownercode,rtc,qtap,uspec,target,lspec,ucl1," +
            " cl1,lcl1,ucl2,cl2,lcl2,ucl3,cl3,lcl3,d_xbar,d_sdev,d_rng,d_unf1,d_unf2,d_max,d_min,d_x,d_rm,d_np,d_p,d_c,d_u,del_mark," +
            "oos,ooc1,ooc2,ooc3,oor1,oor2,oor3,sitecount,rawdatas,dctype,charttype_ext,chartowner,eventuser";
    private static final int DEFAULT_BOOKMARK = 500;
    @Autowired
    private MesRepositoryImpl mes;
    QueryRunner qr_mes = JdbcUtil2.getGpProdRunner();
    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    public int loadData(EtlConfD etlConfD) throws Exception {
       Session session = gpDao.getEntityManagerSession();
        logger.info("{} LoadSpcDataToGpService loadData 开始执行 ！start:{},end:{}",etlConfD.getJobName(),etlConfD.getRunStartTimestamp(),etlConfD.getRunEndTimestamp());
        int count =0;
        String tableNameMes="";
        String tableNameGp="";
        String mesSql="";
        List<String> tabNameTmp=new ArrayList<>();
        List<SpcChartDataTmp> spcChartDataList  = new ArrayList<>();
        Map<String,List<SpcChartDataTmp>> tabMap =  new HashMap<>();
        try {
            List<String> tableFromMesList =getSpcTableFromMes(etlConfD);
            List<String> tableFromGpList =getSpcTableFromGp();
            long startTime=System.currentTimeMillis();
            for( int i=0;i<tableFromMesList.size();i++){
                tableNameMes= tableFromMesList.get(i);
                mesSql  =" select "+MES_QUERY_COLUMN + " from  "+tableNameMes +" CD  where "+
                        "    CD.UPDATETIME >= to_date('"+sdf.format(etlConfD.getRunStartTimestamp())+"','yyyy-mm-dd hh24:mi:ss')  " +
                        "   AND  CD.UPDATETIME< to_date('"+sdf.format(etlConfD.getRunEndTimestamp())+"','yyyy-mm-dd hh24:mi:ss') " +
                        "    and MACHINENAME is not null  " +
                        "  order by MACHINENAME desc";
                spcChartDataList=  qr_mes.query(mesSql, new BeanListHandler<SpcChartDataTmp>(SpcChartDataTmp.class));
            tabMap.put(tableNameMes,spcChartDataList);
            }


            for( int i=0;i<tableFromMesList.size();i++){
                tableNameMes= tableFromMesList.get(i);
                tableNameGp=tableNameMes;
                if(!tableFromGpList.contains(tableNameGp)){
                    tabNameTmp.add(tableNameGp);
                    tableNameGp=tableNameMes.substring(0,tableNameMes.lastIndexOf("_")+1)+"OTHER";
                }
                spcChartDataList=tabMap.get(tableNameMes);
                if(spcChartDataList.size()<=0){ continue;}
            count+= saveSpcDataToGp(session, spcChartDataList,tableNameGp,etlConfD);
            }
            if(tabNameTmp.size()>0){
                logger.info("tableFromGpList 中没有找到的表： {} ",tabNameTmp);
            }
            long endTime=System.currentTimeMillis();
            logger.info("{} loadData 执行结束 {}ms！,count:{}",etlConfD.getJobName(),endTime-startTime,count);

        } catch (Exception e) {
            logger.error(" {} #################：loadData--start:{}--end:{},error{}",etlConfD.getJobName(),etlConfD.getRunStartTimestamp(),etlConfD.getRunEndTimestamp(), LoggerUtil.stackTraceToString(e));
            throw new Exception(e.getMessage());
        }
        return  count;
    }
    public List<String> getSpcTableFromMes(EtlConfD etlConfD){
        List<String> list=new ArrayList<>();
        String shop=etlConfD.getShop();
        String type="Production".equals(etlConfD.getCellType())?"P":"M";
            if(StringUtils.isNotBlank(shop) && StringUtils.isNotBlank(type)  ){
                list =mes.findBySQL(" select t.table_name from user_tables t where regexp_like(t.table_name,'^(SPC_CHART_)+("+type+"_)+("+shop+"_)+') ");
            }else{
                list =mes.findBySQL(" select t.table_name from user_tables t where regexp_like(t.table_name,'^(SPC_CHART_)+(P_|M_)+(ACF_|OC_)+') ");
            }
        return list;
    }
    public List<String> getSpcTableFromGp(){
        List<String> list=new ArrayList<>();
            list =gpDao.findBySQL("  select upper(table_name) table_name from information_schema.tables where table_name ~'^(spc_chart_)+(p_|m_)+(acf_|oc_)+' ");
        return list;
    }
    public int saveSpcDataToGp(Session session,List<SpcChartDataTmp> spcChartDataList,String tableNameGp,EtlConfD etlConfD)throws Exception{
      int count=0;
    //    StringBuffer delSql=new StringBuffer();
      StringBuffer sql=new StringBuffer();
        try{
            String pre="";
            BigDecimal bd = new BigDecimal((double) spcChartDataList.size() / DEFAULT_BOOKMARK).setScale(0, BigDecimal.ROUND_CEILING);
            List<String> delSqlList=new ArrayList<>();
            int n = Integer.parseInt(bd.toString());
            for (int i = 0; i < n; i++) {
                int start = i * DEFAULT_BOOKMARK;
                int end = (i + 1) * DEFAULT_BOOKMARK > spcChartDataList.size() ?spcChartDataList.size(): (i + 1) * DEFAULT_BOOKMARK;
                StringBuffer delSql=new StringBuffer();
                delSql.append(" delete from "+tableNameGp +" where  ");
                for(int j = start; j < end; j++){
                    SpcChartDataTmp spc=spcChartDataList.get(j);
                      pre=spc.getEventtime_pre()!=null?sdf.format(spc.getEventtime_pre().getTime())
                            :(spc.getEventtime()==null?"null":sdf.format(spc.getEventtime().getTime()));
                    delSql.append(" (chartid='"+spc.getChartid()+"' and controldataseq='"+spc.getControldataseq()+"' and eventtime_pre= '"+pre+"') or");

                }
                delSql.deleteCharAt(delSql.length()-1);
                delSql.deleteCharAt(delSql.length()-1);
                delSqlList.add(delSql.toString());
            }

            int delcount=doDefectBySql(delSqlList,session);
               logger.info("删除 {} 表中重复数据 {} 条",tableNameGp,delcount);

          sql.append(" Insert into "+tableNameGp +"(" +GP_QUERY_COLUMN +") values " );
          for(SpcChartDataTmp spc:spcChartDataList){
              sql.append("(");
              sql.append(spc.getChartid()==null?"null":("'"+spc.getChartid().trim()+"'")).append(",");
              sql.append(spc.getControlcharttype()==null?"null":("'"+spc.getControlcharttype().trim()+"'")).append(",");
              sql.append(spc.getFactoryname()==null?"null":("'"+spc.getFactoryname().trim()+"'")).append(",");
              sql.append(spc.getSubfactoryname()==null?"null":("'"+spc.getSubfactoryname().trim()+"'")).append(",");
              sql.append(spc.getControldataseq()==null?"null":("'"+spc.getControldataseq().trim()+"'")).append(",");
              sql.append(spc.getDcdataid()==null?"null":("'"+spc.getDcdataid().trim()+"'")).append(",");
              sql.append(spc.getTimekey()==null?"null":("'"+Timestamp.valueOf(sdf.format(sdf2.parse(spc.getTimekey().substring(0,14)).getTime()))+"'")).append(",");
              sql.append(spc.getMaterialname()==null?"null":("'"+spc.getMaterialname().trim()+"'")).append(",");
              sql.append(spc.getEventtime()==null?"null":("'"+Timestamp.valueOf(sdf.format(spc.getEventtime().getTime()))+"'")).append(",");
             // sql.append(spc.getEventtime_pre()==null?"null":("'"+Timestamp.valueOf(sdf.format(spc.getEventtime_pre().getTime()))+"'")).append(",");
              sql.append(spc.getEventtime_pre()!=null?("'"+Timestamp.valueOf(sdf.format(spc.getEventtime_pre().getTime()))+"'")
                      :(spc.getEventtime()==null?"null":("'"+Timestamp.valueOf(sdf.format(spc.getEventtime().getTime()))+"'"))).append(",");
              sql.append(spc.getAliasitemname()==null?"null":("'"+spc.getAliasitemname().trim()+"'")).append(",");
              sql.append(spc.getProductspecname()==null?"null":("'"+spc.getProductspecname().trim()+"'")).append(",");
              sql.append(spc.getEccode()==null?"null":("'"+spc.getEccode().trim()+"'")).append(",");
              sql.append(spc.getProcessoperationname()==null?"null":("'"+spc.getProcessoperationname().trim()+"'")).append(",");
              sql.append(spc.getMachinename()==null?"null":("'"+spc.getMachinename().trim()+"'")).append(",");
              sql.append(spc.getUnitname()==null?"null":("'"+spc.getUnitname().trim()+"'")).append(",");
              sql.append(spc.getRecipename()==null?"null":("'"+spc.getRecipename().trim() +"'")).append(",");
              sql.append(spc.getProcprocessoperationname()==null?"null":("'"+spc.getProcprocessoperationname().trim() +"'")).append(",");
              sql.append(spc.getProcmachinename()==null?"null":("'"+ spc.getProcmachinename().trim()+"'")).append(",");
              sql.append(spc.getProcunitname()==null?"null":("'"+spc.getProcunitname().trim() +"'")).append(",");
              sql.append(spc.getProcrecipename()==null?"null":("'"+ spc.getProcrecipename().trim()+"'")).append(",");
              sql.append(spc.getRunmode()==null?"null":("'"+spc.getRunmode().trim() +"'")).append(",");
              sql.append(spc.getOwnercode()==null?"null":("'"+ spc.getOwnercode().trim()+"'")).append(",");
              sql.append(spc.getRtc()==null?"null":("'"+ spc.getRtc().trim()+"'")).append(",");
              sql.append(spc.getQtap()==null?"null":("'"+spc.getQtap().trim() +"'")).append(",");
              sql.append(spc.getUspec()==null?"null":("'"+spc.getUspec().trim() +"'")).append(",");
              sql.append(spc.getTarget()==null?"null":("'"+spc.getTarget().trim() +"'")).append(",");
              sql.append(spc.getLspec()==null?"null":("'"+spc.getLspec().trim() +"'")).append(",");
              sql.append(spc.getUcl1()==null?"null":("'"+ spc.getUcl1().trim()+"'")).append(",");
              sql.append(spc.getCl1()==null?"null":("'"+ spc.getCl1().trim()+"'")).append(",");
              sql.append(spc.getLcl1()==null?"null":("'"+spc.getLcl1().trim() +"'")).append(",");
              sql.append(spc.getUcl2()==null?"null":("'"+spc.getUcl2().trim() +"'")).append(",");
              sql.append(spc.getCl2()==null?"null":("'"+spc.getCl2().trim() +"'")).append(",");
              sql.append(spc.getLcl2()==null?"null":("'"+spc.getLcl2().trim() +"'")).append(",");
              sql.append(spc.getUcl3()==null?"null":("'"+spc.getUcl3().trim() +"'")).append(",");
              sql.append(spc.getCl3()==null?"null":("'"+spc.getCl3().trim() +"'")).append(",");
              sql.append(spc.getLcl3()==null?"null":("'"+spc.getLcl3().trim() +"'")).append(",");
              sql.append(spc.getD_xbar()==null?"null":("'"+spc.getD_xbar().trim() +"'")).append(",");
              sql.append(spc.getD_sdev()==null?"null":("'"+spc.getD_sdev().trim() +"'")).append(",");
              sql.append(spc.getD_rng()==null?"null":("'"+ spc.getD_rng().trim()+"'")).append(",");
              sql.append(spc.getD_unf1()==null?"null":("'"+spc.getD_unf1().trim() +"'")).append(",");
              sql.append(spc.getD_unf2()==null?"null":("'"+ spc.getD_unf2().trim()+"'")).append(",");
              sql.append(spc.getD_max()==null?"null":("'"+spc.getD_max().trim() +"'")).append(",");
              sql.append(spc.getD_min()==null?"null":("'"+spc.getD_min().trim() +"'")).append(",");
              sql.append(spc.getD_x()==null?"null":("'"+spc.getD_x().trim() +"'")).append(",");
              sql.append(spc.getD_rm()==null?"null":("'"+ spc.getD_rm().trim()+"'")).append(",");
              sql.append(spc.getD_np()==null?"null":("'"+spc.getD_np().trim()+"'")).append(",");
              sql.append(spc.getD_p()==null?"null":("'"+spc.getD_p().trim()+"'")).append(",");
              sql.append(spc.getD_c()==null?"null":("'"+spc.getD_c().trim() +"'")).append(",");
              sql.append(spc.getD_u()==null?"null":("'"+ spc.getD_u().trim()+"'")).append(",");
              sql.append(spc.getDel_mark()==null?"null":("'"+spc.getDel_mark().trim() +"'")).append(",");
              sql.append(spc.getOos()==null?"null":("'"+ spc.getOos().trim()+"'")).append(",");
              sql.append(spc.getOoc1()==null?"null":("'"+spc.getOoc1().trim() +"'")).append(",");
              sql.append(spc.getOoc2()==null?"null":("'"+ spc.getOoc2().trim()+"'")).append(",");
              sql.append(spc.getOoc3()==null?"null":("'"+spc.getOoc3().trim() +"'")).append(",");
              sql.append(spc.getOor1()==null?"null":("'"+ spc.getOor1().trim()+"'")).append(",");
              sql.append(spc.getOor2()==null?"null":("'"+spc.getOor2().trim() +"'")).append(",");
              sql.append(spc.getOor3()==null?"null":("'"+spc.getOor3().trim() +"'")).append(",");
              sql.append(spc.getSitecount()==null?"null":("'"+ spc.getSitecount().trim()+"'")).append(",");
              sql.append(spc.getRawdatas()==null?"null":("'"+spc.getRawdatas().trim() +"'")).append(",");
              sql.append(spc.getDctype()==null?"null":("'"+spc.getDctype().trim() +"'")).append(",");
              sql.append(spc.getCharttype_ext()==null?"null":("'"+spc.getCharttype_ext().trim() +"'")).append(",");
              sql.append(spc.getChartowner()==null?"null":("'"+spc.getChartowner().trim() +"'")).append(",");
              sql.append(spc.getEventuser()==null?"null":("'"+spc.getEventuser().trim()+"'")).append(" ");
              sql.append("),");
          }

//               SQLQuery queryLoadStepDel = session.createSQLQuery(delSql.toString());
//               queryLoadStepDel.executeUpdate();
          sql.deleteCharAt(sql.length()-1);
          SQLQuery queryLoadStep = session.createSQLQuery(sql.toString());
          count = queryLoadStep.executeUpdate();
      }catch (Exception e){
            logger.error("{} saveSpcDataToGp() tableNameGp:{},error:{}",etlConfD.getJobName(),tableNameGp,e.getMessage());
         throw new Exception("saveSpcDataToGp() "+e+" tableNameGp: "+tableNameGp);
      }
      return count;
    }

        public int doDefectBySql(List<String> sqlList,Session session) throws Exception {
        ThreadPoolExecutor pool = new ThreadPoolExecutor(2,5,300, TimeUnit.SECONDS,
                new ArrayBlockingQueue<>(10)
        );
        int[]  deleteCount={0};
        sqlList.forEach(i -> {
            ExecuteSqlCallable executeSql = new ExecuteSqlCallable(i,session);
            Future<Integer> futureTask = pool.submit(executeSql);
            try {
                deleteCount[0]+=futureTask.get();
            }catch (InterruptedException e) {
                throw new ETLException2(ErrorCode.ERROR_DELETE_SPC_CHART, e);
            } catch (ExecutionException e) {
                throw new ETLException2(ErrorCode.ERROR_DELETE_SPC_CHART, e);
            }
        });
        return deleteCount[0];
    }
}
